package com.css.fxfzypg.modules.model.repository.Impl;

import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.model.entity.DataPbeLosses;
import com.css.fxfzypg.modules.model.entity.EconomicLosses;
import com.css.fxfzypg.modules.model.repository.EconomicLossesRepository;
import com.css.fxfzypg.modules.model.vo.EconomicVO;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.JdbcUtil;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.*;

/**
 * @author Dbj
 * @date 2022年10月06日  15:13
 */
@Repository
public class EconomicLossesRepositoryImpl implements EconomicLossesRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;

    @Resource
    private SUserService sUserService;

    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    private SysAreaService areaService;

    @Override
    public Map<String, Object> findPage(EconomicVO vo, int curPage, int pageSize) {
        StringBuilder sql = new StringBuilder("select * from economic_losses ");
        StringBuilder whereSql = new StringBuilder(" where is_valid ='" + YNEnum.Y.toString() + "'");
        String orderSql = " order by is_default desc,province_code";
        StringBuilder countSql = new StringBuilder("select count(*) from economic_losses ");
        StringBuilder str = new StringBuilder("");
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(PlatformSessionContext.getUserID());
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        if (!vo.getNation()) {
            whereSql.append(" and (org_name = '系统默认' or province='").append(sUser.getProvinceId()).append("')");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getModelName())) {
            whereSql.append(" and model_name like '%" + vo.getModelName() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(vo.getProvince())) {
            if ("cd726776c7de41e4a41dcdb3d90b2806".equals(vo.getProvince()) || "225ac763ab714524b87d95140a93a4a5".equals(vo.getProvince()) || "8a3f7d3f78ad456f92f487a979091c7f".equals(vo.getProvince()) || "afcbf07008a34c2e85917cfd34008033".equals(vo.getProvince())) {
                //拿到北京上海天津重庆四省的省id
                String pid = areaService.getParentId(vo.getProvince());
                vo.setProvince(pid);
            }
            whereSql.append(" and province='").append(vo.getProvince()).append("'");
            ;
        }
        if (!PlatformObjectUtils.isEmpty(vo.getCity())) {
            whereSql.append(" and (position('").append(vo.getCity()).append("' in city) > 0 ");
            whereSql.append(" or city='全部')");
        }
        String limitSql = " limit " + pageSize + " offset " + (curPage - 1) * pageSize;
        String sqlWhole = sql.append(whereSql).append(orderSql).append(limitSql).toString();
        countSql.append(whereSql);
        List<EconomicLosses> list = jdbcTemplate.query(sqlWhole, new BeanPropertyRowMapper<>(EconomicLosses.class));
        Integer total = jdbcTemplate.queryForObject(countSql.toString(), Integer.class);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("total", total);
        map.put("rows", list);
        return map;
    }

    @Override
    public EconomicLosses getModelById(String id) {
        String sql = "select * from economic_losses where is_valid ='" + YNEnum.Y.toString() + "' and id='" + id + "'";
        List<EconomicLosses> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EconomicLosses.class));
        return list.size() > 0 ? list.get(0) : new EconomicLosses();
    }

    @Override
    public EconomicLosses getDefaultModel() {
        String sql = "select * from economic_losses where is_valid ='" + YNEnum.Y.toString() +
                "' and is_default='" + YNEnum.Y.toString() + "' limit 1";
        return jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(EconomicLosses.class));
    }

    @Override
    public List<EconomicLosses> getByDivision(String province, String city, int isNa) {
        String sql = "select * from economic_losses where is_valid ='" + YNEnum.Y.toString() + "' and province='" + province + "'";
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql = sql + " and city='" + city + "'";
        }
        sql = sql + " order by create_time desc ";
        if (isNa == 0) {
            sql = sql + " limit 1 ";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(EconomicLosses.class));
    }

    @Override
    public void save(EconomicLosses economicLosses) {
        String sql =
                "insert into economic_losses(id, model_name, province, city, create_user_id, create_user_name, create_time, "
                        + "org_id,org_name,is_valid,is_default,"
                        + "structure_lossess,"
                        + "indoor_lossess,"
                        + "high_basic_intact,"
                        + "high_slight_damage,"
                        + "high_mid_damage,"
                        + "high_bad_damage,"
                        + "high_destory,"
                        + "steel_basic_intact,"
                        + "steel_slight_intact,"
                        + "steel_mid_intact,"
                        + "steel_bad_intact,"
                        + "steel_destory,"
                        + "multiple_basic_intact,"
                        + "multiple_slight_intact,"
                        + "multiple_mid_intact,"
                        + "multiple_bad_intact,"
                        + "multiple_destory,"
                        + "brick_concrete_basic_intact,"
                        + "brick_concrete_slight_intact,"
                        + "brick_concrete_mid_intact,"
                        + "brick_concrete_bad_intact,"
                        + "brick_concrete_destory,"
                        + "brick_wood_basic_intact,"
                        + "brick_wood_slight_intact,"
                        + "brick_wood_mid_intact,"
                        + "brick_wood_bad_intact,"
                        + "brick_wood_destory,"
                        + "soil_basic_intact,"
                        + "soil_slight_intact,"
                        + "soil_mid_intact,"
                        + "soil_bad_intact,"
                        + "soil_destory,"
                        + "wood_basic_intact,"
                        + "wood_slight_intact,"
                        + "wood_mid_intact,"
                        + "wood_bad_intact,"
                        + "wood_destory,"
                        + "stone_basic_intact,"
                        + "stone_slight_intact,"
                        + "stone_mid_intact,"
                        + "stone_bad_intact,"
                        + "stone_destory,"
                        + "other_basic_intact,"
                        + "other_slight_intact,"
                        + "other_mid_intact,"
                        + "other_bad_intact,"
                        + "other_destory,"
                        + "life_ratio1,"
                        + "life_ratio2,"
                        + "other_ratio1,"
                        + "other_ratio2,province_code)"
                        + " values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql, economicLosses.getId(), economicLosses.getModelName(), economicLosses.getProvince(), economicLosses.getCity(), economicLosses.getCreateUserId(), economicLosses.getCreateUserName(), economicLosses.getCreateTime(),
                economicLosses.getOrgId(), economicLosses.getOrgName(), economicLosses.getIsValid(), economicLosses.getIsDefault(), economicLosses.getStructureLossess(),
                economicLosses.getIndoorLossess(), economicLosses.getHighBasicIntact(), economicLosses.getHighSlightDamage(), economicLosses.getHighMidDamage(), economicLosses.getHighBadDamage(), economicLosses.getHighDestory(),
                economicLosses.getSteelBasicIntact(), economicLosses.getSteelSlightIntact(), economicLosses.getSteelMidIntact(), economicLosses.getSteelBadIntact(), economicLosses.getSteelDestory(),
                economicLosses.getMultipleBasicIntact(), economicLosses.getMultipleSlightIntact(), economicLosses.getMultipleMidIntact(), economicLosses.getMultipleBadIntact(), economicLosses.getMultipleDestory(),
                economicLosses.getBrickConcreteBasicIntact(), economicLosses.getBrickConcreteSlightIntact(), economicLosses.getBrickConcreteMidIntact(), economicLosses.getBrickConcreteBadIntact(), economicLosses.getBrickConcreteDestory(),
                economicLosses.getBrickWoodBasicIntact(), economicLosses.getBrickWoodSlightIntact(), economicLosses.getBrickWoodMidIntact(), economicLosses.getBrickWoodBadIntact(), economicLosses.getBrickWoodDestory(),
                economicLosses.getSoilBasicIntact(), economicLosses.getSoilSlightIntact(), economicLosses.getSoilMidIntact(), economicLosses.getSoilBadIntact(), economicLosses.getSoilDestory(),
                economicLosses.getWoodBasicIntact(), economicLosses.getWoodSlightIntact(), economicLosses.getWoodMidIntact(), economicLosses.getWoodBadIntact(), economicLosses.getWoodDestory(),
                economicLosses.getStoneBasicIntact(), economicLosses.getStoneSlightIntact(), economicLosses.getStoneMidIntact(), economicLosses.getStoneBadIntact(), economicLosses.getStoneDestory(),
                economicLosses.getOtherBasicIntact(), economicLosses.getOtherSlightIntact(), economicLosses.getOtherMidIntact(), economicLosses.getOtherBadIntact(), economicLosses.getOtherDestory(),
                economicLosses.getLifeRatio1(), economicLosses.getLifeRatio2(), economicLosses.getOtherRatio1(), economicLosses.getOtherRatio2(), economicLosses.getProvinceCode());
    }

    @Override
    public void update(EconomicLosses economicLosses) {
        String sql =
                "update economic_losses set model_name=?,"
                        + "structure_lossess=?,"
                        + "indoor_lossess=?,"
                        + "high_basic_intact=?,"
                        + "high_slight_damage=?,"
                        + "high_mid_damage=?,"
                        + "high_bad_damage=?,"
                        + "high_destory=?,"
                        + "steel_basic_intact=?,"
                        + "steel_slight_intact=?,"
                        + "steel_mid_intact=?,"
                        + "steel_bad_intact=?,"
                        + "steel_destory=?,"
                        + "multiple_basic_intact=?,"
                        + "multiple_slight_intact=?,"
                        + "multiple_mid_intact=?,"
                        + "multiple_bad_intact=?,"
                        + "multiple_destory=?,"
                        + "brick_concrete_basic_intact=?,"
                        + "brick_concrete_slight_intact=?,"
                        + "brick_concrete_mid_intact=?,"
                        + "brick_concrete_bad_intact=?,"
                        + "brick_concrete_destory=?,"
                        + "brick_wood_basic_intact=?,"
                        + "brick_wood_slight_intact=?,"
                        + "brick_wood_mid_intact=?,"
                        + "brick_wood_bad_intact=?,"
                        + "brick_wood_destory=?,"
                        + "soil_basic_intact=?,"
                        + "soil_slight_intact=?,"
                        + "soil_mid_intact=?,"
                        + "soil_bad_intact=?,"
                        + "soil_destory=?,"
                        + "wood_basic_intact=?,"
                        + "wood_slight_intact=?,"
                        + "wood_mid_intact=?,"
                        + "wood_bad_intact=?,"
                        + "wood_destory=?,"
                        + "stone_basic_intact=?,"
                        + "stone_slight_intact=?,"
                        + "stone_mid_intact=?,"
                        + "stone_bad_intact=?,"
                        + "stone_destory=?,"
                        + "other_basic_intact=?,"
                        + "other_slight_intact=?,"
                        + "other_mid_intact=?,"
                        + "other_bad_intact=?,"
                        + "other_destory=?,"
                        + "life_ratio1=?,"
                        + "life_ratio2=?,"
                        + "other_ratio1=?,"
                        + "other_ratio2=?,update_user_id=?,update_time=? where id =?";
        jdbcTemplate.update(sql, economicLosses.getModelName(), economicLosses.getStructureLossess(),
                economicLosses.getIndoorLossess(), economicLosses.getHighBasicIntact(), economicLosses.getHighSlightDamage(), economicLosses.getHighMidDamage(), economicLosses.getHighBadDamage(), economicLosses.getHighDestory(),
                economicLosses.getSteelBasicIntact(), economicLosses.getSteelSlightIntact(), economicLosses.getSteelMidIntact(), economicLosses.getSteelBadIntact(), economicLosses.getSteelDestory(),
                economicLosses.getMultipleBasicIntact(), economicLosses.getMultipleSlightIntact(), economicLosses.getMultipleMidIntact(), economicLosses.getMultipleBadIntact(), economicLosses.getMultipleDestory(),
                economicLosses.getBrickConcreteBasicIntact(), economicLosses.getBrickConcreteSlightIntact(), economicLosses.getBrickConcreteMidIntact(), economicLosses.getBrickConcreteBadIntact(), economicLosses.getBrickConcreteDestory(),
                economicLosses.getBrickWoodBasicIntact(), economicLosses.getBrickWoodSlightIntact(), economicLosses.getBrickWoodMidIntact(), economicLosses.getBrickWoodBadIntact(), economicLosses.getBrickWoodDestory(),
                economicLosses.getSoilBasicIntact(), economicLosses.getSoilSlightIntact(), economicLosses.getSoilMidIntact(), economicLosses.getSoilBadIntact(), economicLosses.getSoilDestory(),
                economicLosses.getWoodBasicIntact(), economicLosses.getWoodSlightIntact(), economicLosses.getWoodMidIntact(), economicLosses.getWoodBadIntact(), economicLosses.getWoodDestory(),
                economicLosses.getStoneBasicIntact(), economicLosses.getStoneSlightIntact(), economicLosses.getStoneMidIntact(), economicLosses.getStoneBadIntact(), economicLosses.getStoneDestory(),
                economicLosses.getOtherBasicIntact(), economicLosses.getOtherSlightIntact(), economicLosses.getOtherMidIntact(), economicLosses.getOtherBadIntact(), economicLosses.getOtherDestory(),
                economicLosses.getLifeRatio1(), economicLosses.getLifeRatio2(), economicLosses.getOtherRatio1(), economicLosses.getOtherRatio2(), economicLosses.getUpdateUserId(), economicLosses.getUpdateTime(), economicLosses.getId());
    }

    @Override
    public void delete(String userId, Date updateTime, String id) {
        jdbcTemplate.update("update economic_losses set is_valid=?,update_user_id=?,update_time=? where id=?", YNEnum.N.toString(), userId, updateTime, id);
    }

    @Override
    public void deleteByTaskId(String taskId) {
        //jdbcTemplate.update("delete from  data_pbe_losses  where task_id=?",taskId);
       // String sql = "drop table data_pbe_losses_" + taskId;
        String sql = "delete from  data_pbe_losses  where task_id='"+taskId+"'";
        JdbcUtil.executeSql(sql);
    }

    @Override
    public void saveDataPbeLosses(DataPbeLosses losses) {
        jdbcTemplate.update("insert into data_pbe_losses(id,pbe_id,population,coveredarea,structure_losses,indoor_losses,structure_direct_losses,life_losses,other_losses,direct_losses,dz_intentity,task_id,geom_text) " +
                        "values(?,?,?,?,?,?,?,?,?,?,?,?,?)", losses.getId(), losses.getPbeId(), losses.getPopulation(), losses.getCoveredarea(), losses.getStructureLosses(), losses.getIndoorLosses(),
                losses.getStructureDirectLosses(), losses.getLifeLosses(), losses.getOtherLosses(), losses.getDirectLosses(), losses.getDzIntentity(), losses.getTaskId(), losses.getGeomText());
    }

    @Override
    public List<Map<String, Object>> getPbeLossesByTaskId(String taskId,String range) {
        String sql="";
        if ("0".equals(range)){
            sql = "select a.pbe_id pbeId,st_area(st_intersection(ST_MakeValid(st_geometryfromtext(a.geom_text,4490)), b.geom)) as area,a.direct_losses directLosses," +
                    "b.fault_num sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from data_pbe_losses a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num,c.earthquake_level,c.angle,b.source_earthquake_id " +
                    " from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id" +
                    " where b.task_id='" + taskId + "' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intentity=b.dz_intensity and st_intersects(st_geometryfromtext(a.geom_text,4490),b.geom)";
        }else {
            sql = "select a.pbe_id pbeId,st_area(st_intersection(ST_MakeValid(st_geometryfromtext(a.geom_text,4490)), b.geom)) as area,a.direct_losses directLosses," +
                    "b.fault_num sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from data_pbe_losses a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num,c.earthquake_level,c.angle,b.source_earthquake_id " +
                    " from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id" +
                    " where b.task_id='" + taskId + "' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intentity=b.dz_intensity and st_intersects(st_geometryfromtext(a.geom_text,4490),b.geom)" +
                    "AND st_intersects ( st_geometryfromtext ( a.geom_text, 4490 ), (SELECT geom from ypg_preassess_task where id ='"+taskId+"') )";
        }
        return jdbcTemplate.queryForList(sql);
    }

    @Override
    public List<DataPbeLosses> getPbeLosses(String taskId, String dzIntensity) {
        /*String sql =
        "SELECT *,ST_AsText(st_intersection((select st_union(a.geom,(select b.geom from ypg_preassess_task b " +
                "where id='"+taskId+"')) as geom from " +
                "(select st_union(c.geom) as geom from setting_seismic_influenceflied c where c.task_id='"+taskId+"') a),st_geometryfromtext(s.geom_text,4490))) geomText " +
                "FROM (SELECT * FROM data_pbe_losses t WHERE ST_Intersects(st_geometryfromtext(t.geom_text,4490),(select st_union(a.geom,(select b.geom from ypg_preassess_task b where id='"+taskId+"')) as geom " +
                "from (select st_union(c.geom) as geom from setting_seismic_influenceflied c where c.task_id='"+taskId+"') a)) and dz_intentity='"+dzIntensity+"' and task_id='"+taskId+"') s";*/
        String sql = "select * from data_pbe_losses where task_id='" + taskId + "'";
        if (PlatformObjectUtils.isNotEmpty(dzIntensity)) {
            sql = sql + " and dz_intentity='" + dzIntensity + "'";
        }
        List<DataPbeLosses> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeLosses.class));
        return list == null ? new ArrayList<>(0) : list;
    }

    @Override
    public DataPbeLosses findByTaskIdLimit(String taskId) {
        String sql = "select * from data_pbe_losses where task_id='" + taskId + "' limit 1";
        List<DataPbeLosses> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(DataPbeLosses.class));
        return list.size() < 1 ? null : list.get(0);
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRES_NEW)
    public int saveDataPbeLossesList(List<DataPbeLosses> list) {
        String sql = "insert into data_pbe_losses(id,pbe_id,population,coveredarea,structure_losses,indoor_losses," +
                "structure_direct_losses,life_losses,other_losses,direct_losses,dz_intentity,task_id,geom_text) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
        int[] updateCountArray = jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, list.get(i).getId());
                ps.setString(2, list.get(i).getPbeId());
                ps.setInt(3, list.get(i).getPopulation());
                ps.setFloat(4, list.get(i).getCoveredarea().floatValue());
                ps.setFloat(5, list.get(i).getStructureLosses().floatValue());
                ps.setFloat(6, list.get(i).getIndoorLosses().floatValue());
                ps.setFloat(7, list.get(i).getStructureDirectLosses().floatValue());
                ps.setFloat(8, list.get(i).getLifeLosses().floatValue());
                ps.setFloat(9, list.get(i).getOtherLosses().floatValue());
                ps.setFloat(10, list.get(i).getDirectLosses().floatValue());
                ps.setString(11, list.get(i).getDzIntentity());
                ps.setString(12, list.get(i).getTaskId());
                ps.setString(13, list.get(i).getGeomText());
            }

            @Override
            public int getBatchSize() {

                return list.size();
            }
        });
        int sumInsertedCount = 0;
        for (int a : updateCountArray) {
            sumInsertedCount += a;
        }
        return sumInsertedCount;
    }

    @Override
    public void createLossesFqRel(String taskId) {
        String createSql = "create table data_pbe_losses_" + taskId +
                " PARTITION OF data_pbe_losses for values in ('" + taskId + "')";
        JdbcUtil.executeSql(createSql);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from economic_losses where province = '" + province + "'  and is_valid ='1' ", Integer.class);
    }

    @Override
    public Integer countByYearPrefixLike(String taskYear, String provinceId) {
            return jdbcTemplate.queryForObject("SELECT count(*) FROM economic_losses where (TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%' or TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE '" + taskYear + "%') and province = '" + provinceId + "'  and is_valid ='1' ", Integer.class);
    }
}
